import os

import django
from django.core.files.storage import default_storage

from course.models import Node, NodeAcl

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'venus.settings')
django.setup()


class LocalAPIView():
    def _can_show(self, _node, subject_id, site_id):
        """
        在知识点上增加:
            is_visible字段，用于前端判断是否显示
            chapter_nodes字段，用于前端判断是否关联映射章节

        :param _node:
        :return: is_visible
        """
        # since 23.6.15 将chapter_nodes转换为[{id, title}]
        # if format_mapped_name:
        #     _node['chapter_nodes'] = [
        #         {'id': cn, 'title': tk_node_dict.get(cn)}
        #         for cn in (_node.get('chapter_nodes') or []) if tk_node_dict.get(cn)
        #     ]
        _node['subject_id'] = subject_id
        _node['is_owner'] = _node.get('site_id', 1) == site_id
        # if include_deleted:
        #     _node['is_visible'], is_hidden = NodeAcl.objects.visible(
        #         site_id,
        #         dict(
        #             id=_node.get('id'),
        #             site_id=_node.get('site_id'),
        #             subject_id=_node.get('subject_id'),
        #             category=_node.get('category')
        #         ),  # since 24.12.20 指定参数
        #         include_hidden=include_deleted
        #     )
        # else:
        include_deleted = False
        is_hidden = False
        _node['is_visible'] = NodeAcl.objects.visible(
            site_id, _node,
            include_hidden=include_deleted
        )

        return _node['is_visible']

    def _filter_acl(self, nodes):
        # 过滤掉不显示的
        nodes = [node for node in nodes if self._can_show(node, subject_id=1, site_id=460)]
        for node in nodes:
            # print("node['is_owner']", node['is_owner'])
            # if node.get('site_id') and node['site_id'] != 1 and not node['is_owner']:
            #     continue
            children = node.get('children', [])
            node['children'] = self._filter_acl(children)

        return nodes

    def get(self):
        from course.cache import node_cache
        subject_ids = [1, 2, 3, 5, 6, 7, 8, 9, 10]
        subject_ids = [1]
        question_data_2020 = self.select_sql(1, paper_ids=[2740134, 2622954, 2623099, 2623127, 7915191, 2609995])
        question_data_2021 = self.select_sql(1, paper_ids=[6919702, 6941069, 6941483, 6593220, 7303771])
        question_data_2022 = self.select_sql(1, paper_ids=[11211704, 11286289, 12089632, 11205718, 11236444])
        question_data_2023 = self.select_sql(1, paper_ids=[18147460, 17941948, 17886966, 17887147])
        question_data_2024 = self.select_sql(1, paper_ids=[27817106, 30996235, 27792128, 28999667])
        for subject_id in subject_ids:

            node_graph = node_cache.get(
                subject_id=subject_id,
                category=1
            )
            # print(node_graph)
            # print(node_graph[0])
            node_graph = [i for i in node_graph if i['title'] == '知识点']
            node_graph = node_graph[0]['children']

            node_list = self._filter_acl(node_graph)
            node_list.sort(key=lambda x: (x['show_order'], x['id']))
            pu_node_dict = {}
            for i in node_list:
                cc = 1
                i['children'].sort(key=lambda x: (x['show_order'], x['id']))
                pu_node_dict[i['id']] = {'id': i['id'], 'title': i['title'], 'cc': 1}
                for i2 in i['children']:
                    cc = 2
                    pu_node_dict[i2['id']] = {'id': i2['id'], 'title': i2['title'], 'cc': 2}
                    i2['children'].sort(key=lambda x:  (x['show_order'], x['id']))
                    for i3 in i2['children']:
                        cc = 3
                        pu_node_dict[i3['id']] = {'id': i3['id'], 'title': i3['title'], 'cc': 3}
                        i3['children'].sort(key=lambda x:  (x['show_order'], x['id']))
                        for i4 in i3['children']:
                            cc = 4
                            pu_node_dict[i4['id']] = {'id': i4['id'], 'title': i4['title'], 'cc': 4}
            for k, v in pu_node_dict.items():
                pu_node_dict[k].setdefault('2020_0', [])
                pu_node_dict[k].setdefault('2020_2', [])
                pu_node_dict[k].setdefault('2021_0', [])
                pu_node_dict[k].setdefault('2021_2', [])
                pu_node_dict[k].setdefault('2022_0', [])
                pu_node_dict[k].setdefault('2022_2', [])
                pu_node_dict[k].setdefault('2023_0', [])
                pu_node_dict[k].setdefault('2023_2', [])
                pu_node_dict[k].setdefault('2024_0', [])
                pu_node_dict[k].setdefault('2024_2', [])
                for j in question_data_2020:
                    if str(k) in j['merged_parents']:
                        if j['model'] in [0, 1]:
                            pu_node_dict[k]['2020_0'].append(j['question_id'])
                        else:
                            pu_node_dict[k]['2020_2'].append(j['question_id'])
                for j in question_data_2021:
                    if str(k) in j['merged_parents']:
                        if j['model'] in [0, 1]:
                            pu_node_dict[k]['2021_0'].append(j['question_id'])
                        else:
                            pu_node_dict[k]['2021_2'].append(j['question_id'])
                for j in question_data_2022:
                    if str(k) in j['merged_parents']:
                        if j['model'] in [0, 1]:
                            pu_node_dict[k]['2022_0'].append(j['question_id'])
                        else:
                            pu_node_dict[k]['2022_2'].append(j['question_id'])
                for j in question_data_2023:
                    if str(k) in j['merged_parents']:
                        if j['model'] in [0, 1]:
                            pu_node_dict[k]['2023_0'].append(j['question_id'])
                        else:
                            pu_node_dict[k]['2023_2'].append(j['question_id'])
                for j in question_data_2024:
                    if str(k) in j['merged_parents']:
                        if j['model'] in [0, 1]:
                            pu_node_dict[k]['2024_0'].append(j['question_id'])
                        else:
                            pu_node_dict[k]['2024_2'].append(j['question_id'])

            for node_id in pu_node_dict:
                is_zt = False
                v = pu_node_dict[node_id]
                if v['2020_2'] or v['2020_0'] or v['2021_2'] or v['2021_0'] or v['2022_2'] or v['2022_0'] or v[
                    '2023_2'] or v['2023_0'] or v['2024_2'] or v['2024_0']:
                    is_zt = True
                pu_node_dict[node_id]['is_zt'] = is_zt
                kp_num = 0

                if v['2020_2'] or v['2020_0']:
                    kp_num += 1
                if v['2021_2'] or v['2021_0']:
                    kp_num += 1
                if v['2022_2'] or v['2022_0']:
                    kp_num += 1
                if v['2023_2'] or v['2023_0']:
                    kp_num += 1
                if v['2024_2'] or v['2024_0']:
                    kp_num += 1
                question_count_0 = len(v['2020_0']) + len(v['2021_0']) + len(v['2022_0']) + len(v['2023_0']) + len(
                    v['2024_0'])
                question_count_2 = len(v['2020_2']) + len(v['2021_2']) + len(v['2022_2']) + len(v['2023_2']) + len(
                    v['2024_2'])
                question_count = question_count_0 + question_count_2
                pu_node_dict[node_id]['kp_num'] = kp_num
                pu_node_dict[node_id]['question_count_0'] = question_count_0
                pu_node_dict[node_id]['question_count_2'] = question_count_2
                pu_node_dict[node_id]['question_count'] = question_count
            import openpyxl
            wb = openpyxl.load_workbook(default_storage.path('AAA.xlsx'))
            sheet = wb['真题数据']
            node_list = node_cache.get_tradition_node_flat_list(
                subject_id=subject_id,
                category_id=Node.CATEGORY_TRADITION
            )

            for index, k in enumerate(pu_node_dict):
                v = pu_node_dict[k]
                sheet.cell(row=index + 3, column=1).value = v['cc']
                sheet.cell(row=index + 3, column=v['cc'] + 1).value = v['title']
                sheet.cell(row=index + 3, column=6).value = '是' if v['is_zt'] else '否'
                sheet.cell(row=index + 3, column=7).value = f"{v['kp_num']}/5"
                sheet.cell(row=index + 3, column=8).value = v['question_count']
                sheet.cell(row=index + 3, column=9).value = v['question_count_0']
                sheet.cell(row=index + 3, column=10).value = v['question_count_2']
                # 2024
                sheet.cell(row=index + 3, column=11).value = len(v['2024_0']) + len(v['2024_2'])
                sheet.cell(row=index + 3, column=12).value = len(v['2024_0'])
                sheet.cell(row=index + 3, column=13).value = len(v['2024_2'])
                # 2023
                sheet.cell(row=index + 3, column=14).value = len(v['2023_0']) + len(v['2023_2'])
                sheet.cell(row=index + 3, column=15).value = len(v['2023_0'])
                sheet.cell(row=index + 3, column=16).value = len(v['2023_2'])
                # 2022
                sheet.cell(row=index + 3, column=17).value = len(v['2022_0']) + len(v['2022_2'])
                sheet.cell(row=index + 3, column=18).value = len(v['2022_0'])
                sheet.cell(row=index + 3, column=19).value = len(v['2022_2'])
                # 2021
                sheet.cell(row=index + 3, column=20).value = len(v['2021_0']) + len(v['2021_2'])
                sheet.cell(row=index + 3, column=21).value = len(v['2021_0'])
                sheet.cell(row=index + 3, column=22).value = len(v['2021_2'])
                # 2020
                sheet.cell(row=index + 3, column=23).value = len(v['2020_0']) + len(v['2020_2'])
                sheet.cell(row=index + 3, column=24).value = len(v['2020_0'])
                sheet.cell(row=index + 3, column=25).value = len(v['2020_2'])
            wb.save(default_storage.path('AAA1_数学.xlsx'))
            return node_list

    def select_sql(self, subject_id, paper_ids):

        from rest_api.questions.data import subject_data

        _data = subject_data.get(subject_id)
        if _data:
            return _data

        # 1
        # paper_id_dict = {
        #     # 1: [ 2623127,2623099,2740134,7915191,2609995,2622954,6941483,6941069,7303771,6593220,6919702,11211704,12089632,11205718,11236444,11286289,18147460,17941948,17886966,17887147,27817106,27817113,28999667,27792630],
        #     1: [74475, 32485, 32509, 3485, 3508, 5204, 7682, 8473, 10407, 14803],
        # }
        # paper_ids = paper_id_dict.get(subject_id)
        sql = f"""
        with
            my_question_node as (
            select
                question_id,
                model,
                arrayJoin(itembank_questionnode.node_list) as node_id from itembank_question
            join itembank_questionnode  on itembank_question.id = itembank_questionnode.question_id
            where
                subject_id = {subject_id}
                and node_id > 0
                and id in (
                select question_id from itembank_paperquestion where paper_id in {paper_ids}
            )

            ),
            aaa as (
                select
                question_id,
                 model,
                node_id,
                arrayConcat(arrayMap(x -> trim(BOTH ' ' FROM x), arrayFilter(x -> x != '',splitByString('.',parent_path))),[toString(id)]) as parents
            from my_question_node
            join course_node on course_node.id = my_question_node.node_id
            order by node_id
            )
            select question_id,
             model,
                   arrayDistinct(arrayFlatten(groupArray(parents))) AS merged_parents
            from aaa group by question_id, model
        """
        from galaxy.dao.base import ClickhouseDao
        # print(sql)
        x = list(ClickhouseDao()._fetch_all(sql, converter=lambda item: item.to_dict()))
        # print(x)
        return x
